import pymysql
import json
import datetime

# 生产
# host = '10.1.60.102'
host = '47.93.50.165'
user = 'sr_read'
password = 'Gigqh4UvKYpwPEuH'
database = 'master_data'
charset = 'utf8'


# 测试
# host = 'rm-2ze86ia90s73c90pw0o.mysql.rds.aliyuncs.com'
# user = 'cloudpc_dev'
# password = 'w0RZMCO_La3o'
# database = 'cloudpc_dev'
# charset = 'utf8'


def get_connection():
    con = pymysql.connect(host=host, user=user, password=password, db=database,
                          charset=charset,port=29030)
    return con


# 插入数据，如需自定义表名，则传参table_name; 如有自增列，则传参auto_id
def insert(obj, table_name='', auto_id=''):
    con = get_connection()
    attr_dict = obj.__dict__
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "insert into " + table_name + " ( "
    for key in attr_dict:
        if key != auto_id:
            sql = sql + " `" + str(key) + "` , "
    sql = sql[:-2] + ")"

    sql = sql + ' values ( '
    for key in attr_dict:
        if key != auto_id:
            sql = sql + "'" + str(attr_dict[key]) + "' , "

    sql = sql[:-2] + " ) "

    print(sql)
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


# 更新数据，如需自定义表名，则传参table_name; 如有条件，则传参id_key
def update(obj, table_name='', id_key=''):
    con = get_connection()
    attr_dict = obj.__dict__
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "update " + table_name + " set "
    for key, value in attr_dict.items():
        if key != id and key != '' and key is not None:
            sql = sql + " `" + key + "` = '" + str(value) + "' , "

    sql = sql[:-2]

    if id_key != '':
        sql = sql + " where `" + id_key + "` = " + " '" + str(attr_dict[id_key]) + "' "

    print(sql)
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


def execute(sql):
    con = get_connection()
    print(sql)
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


def delete(obj, id_key, table_name=''):
    con = get_connection()
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "delete from " + table_name + "where id_key = " + id_key
    cur = con.cursor()
    cur.execute(sql)
    con.commit()
    con.close()


# 查询操作;返回一个二维元组
def select(obj, table_name=''):
    con = get_connection()
    if table_name == '':
        table_name = table_name_format(obj.__class__.__name__)
    sql = "select * from " + table_name
    if obj != '' and obj is not None:
        sql = sql + " where "
        attr_dict = obj.__dict__
        for key, value in attr_dict.items():
            sql = sql + "`" + key + "` = " + " '" + str(value) + "' and "
        sql = sql[:-4]

    cur = con.cursor()
    cur.execute(sql)
    res = cur.fetchall()
    con.close()
    return res


def select_by_sql(sql):
    con = get_connection()
    cursor = con.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    rows = []
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = str(value)
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    con.close()
    return js
    con.close()
    return res


def select_by_sql2(sql, cursor):
    # con = get_connection()
    # cursor = con.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    rows = []
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = str(value)
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    # con.close()
    return js
    # con.close()
    # return res


# 表名格式化
def table_name_format(table_name):
    lst = []
    for index, char in enumerate(table_name):
        if char.isupper() and index != 0:
            lst.append("_")
        lst.append(char)

    return "".join(lst).lower()


def test():
    db = pymysql.connect(host=host, user=user, password=password, db=db,
                         charset=charset)

    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()

    # 使用 execute()  方法执行 SQL 查询
    cursor.execute("SELECT VERSION()")

    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()

    print("数据库连接成功！")

    # 关闭数据库连接
    db.close()


if __name__ == '__main__':
    test()
